/*
 * Copyright 2013 NJUT  qixiaobo. All rights reserved.
 */
package com.fujitsu.nanjing.mobile.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Blob;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * ExcelTool.java
 * 
 * @author Administrator
 */
public class ExcelUtils<T> {

	private Class<T> clazz = null;
	private POIFSFileSystem fs;
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFRow row;
	private Field[] fields;
	private String title[];
	public final static int MaxReadOrWriteLine = 100;
	private final static String SerialVersionUID = "serialVersionUID";

	public ExcelUtils(Class<T> clazz) {
		this.clazz = clazz;
	}

	public List<T> readExcel(String path, int limit)
			throws FileNotFoundException, SecurityException,
			NoSuchFieldException, IllegalArgumentException,
			InstantiationException, IllegalAccessException,
			NoSuchMethodException, InvocationTargetException {
		File file = new File(path);
		InputStream is = new FileInputStream(file);
		readExcelTitle(is);
		is = new FileInputStream(file);
		List<T> list = readExcelContent(is, limit);
		return list;
	}
	public void writeExcel(OutputStream os, List<T> list, String title)
			throws IOException, IllegalAccessException,
			InvocationTargetException, NoSuchMethodException {
		wb = new HSSFWorkbook();// HSSFWorkbook
		sheet = wb.createSheet();//
		wb.setSheetName(0, title);
		writeTitle();
		int i = 1;
		for (T t : list) {
			row = sheet.createRow((short) i);
			HSSFCell cell;
			int j = 0;
			for (Field field : fields) {
				if (isSerialVersionUID(field)) {
					continue;
				}
				cell = row.createCell(j);
				cell.setCellValue(BeanUtilsEx.getProperty(t, field.getName()));
				j++;
			}
			i++;
		}
		wb.write(os);
		os.close();
	}
	/**
	 * 
	 * 写excel标题
	 * **/
	private void writeTitle() {
		row = sheet.createRow((short) 0);
		fields = clazz.getDeclaredFields();
		HSSFCell cell;
		int i = 0;
		for (Field field : fields) {
			if (isSerialVersionUID(field)) {
				continue;
			}
			cell = row.createCell(i);
			cell.setCellValue(field.getName());
			i++;
		}
	}
	private boolean isSerialVersionUID(Field field) {
		if (field.getName().equals(SerialVersionUID)) {
			return true;
		}
		return false;
	}
	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @param InputStream
	 * @return String 表头内容的数组
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 */
	private void readExcelTitle(InputStream is) throws SecurityException,
			NoSuchFieldException {
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		fields = new Field[colNum];
		System.out.println("colNum:" + colNum);
		title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			// title[i] = getStringCellValue(row.getCell((short) i));
			title[i] = getCellFormatValue(row.getCell(i));
			fields[i] = clazz.getDeclaredField(title[i]);
		}
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return List 包含单元格数据内容的List对象
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws InvocationTargetException
	 * @throws IllegalArgumentException
	 */
	private List<T> readExcelContent(InputStream is, int limit)
			throws InstantiationException, IllegalAccessException,
			SecurityException, NoSuchMethodException, IllegalArgumentException,
			InvocationTargetException {
		List<T> list = new ArrayList<T>();
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		int limitEnd = (limit + MaxReadOrWriteLine) > rowNum ? rowNum : limit
				+ MaxReadOrWriteLine;
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 1 + limit; i <= limitEnd; i++) {
			row = sheet.getRow(i);
			int j = 0;
			T t = clazz.newInstance();
			while (j < colNum) {
				Object value = getCellValue(row.getCell(j));
				BeanUtilsEx.setProperty(t, title[j], value);
				j++;
			}
			list.add(t);

		}
		return list;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING :
				strCell = cell.getStringCellValue();
				
				break;
			case HSSFCell.CELL_TYPE_NUMERIC :
				strCell= String.valueOf(cell.getNumericCellValue());
				if(strCell.endsWith(".0")){
                    strCell=strCell.substring(0, strCell.length()-2);
				}
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN :
				strCell= String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_BLANK :
				strCell = "";
				break;
			default :
				strCell = "";
				break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
				case HSSFCell.CELL_TYPE_NUMERIC :
				case HSSFCell.CELL_TYPE_FORMULA : {
					// 判断当前的cell是否为Date
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						// 如果是Date类型则，转化为Data格式

						// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
						// cellvalue = cell.getDateCellValue().toLocaleString();

						// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
						Date date = cell.getDateCellValue();
						SimpleDateFormat sdf = new SimpleDateFormat(
								"yyyy-MM-dd");
						cellvalue = sdf.format(date);

					}
					// 如果是纯数字
					else {
						// 取得当前Cell的数值
						cellvalue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				}
				// 如果当前Cell的Type为STRIN
				case HSSFCell.CELL_TYPE_STRING :
					// 取得当前的Cell字符串
					cellvalue = cell.getRichStringCellValue().getString();
					break;
				// 默认的Cell值
				default :
					cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;

	}
	public byte[] blob2ByteArr(Blob blob) throws Exception {

		byte[] b = null;
		try {
			if (blob != null) {
				long in = 0;
				b = blob.getBytes(in, (int) (blob.length()));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("fault");
		}

		return b;
	}
	@SuppressWarnings("unused")
    private void writePhoto(byte[] content,HSSFClientAnchor anchor ) throws IOException {
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		patriarch.createPicture(anchor,
				wb.addPicture(content, HSSFWorkbook.PICTURE_TYPE_JPEG));

	}

}
